In database systems, many resources (such as data blocks, tables, indexes) are shared among multiple processes. Even though resources may be shared, many resources may not be used by more than one process at a given time. For example, sometimes, tables stored on a storage medium may be concurrently accessed in some ways (e.g., read) by multiple processes, but accessed in other ways (e.g., written to) by only one process at a time. As a result, mechanisms have been developed to control access to resources.
One such mechanism uses locks. A lock is a data structure that indicates that a particular process has been granted certain rights with respect to a resource. There are many types of locks, some of which may be shared by many processes, while other types prevent any other locks from being granted on the same resource.
A database transaction includes one or more database statements. A database statement that specifies a change to a database object (such as a table or an index) is referred to as a data manipulation language (DML) statement. A database statement that specifies a change to metadata (i.e., at least a portion thereof) of a database object is referred to as a data definition language (DDL) statement. Even though a process acquires and holds a lock, a database statement is also said to acquire and hold a lock.
In high traffic database systems, it is important to allow multiple DML statement s to update a single table concurrently. However, if locks were granted to DML statements on a per-table basis, then only one DML statement would be able to access a table at any instant. Mechanisms have therefore been developed for allowing multiple DML statements to update a single table. DML statements may obtain locks at the granularity of a single row of a table in order to allow other DML statements to concurrently acquire locks on other rows of the table. Such DML statements typically also acquire an object-level lock (e.g., a lock on the entire table) in SX mode (described in more detail below) that prevents other process from acquiring incompatible object-level locks on the same object.
However, some DDL statements require a lock on an entire table. Once issued, such a DDL statement must wait for any DML statements that currently hold locks on individual rows of the table to release their locks before the DDL statement begins. Once the table lock is acquired, any DML statements that require a lock on individual rows of the same table must wait until the DDL statement completes.
FIG. 1 is a graph that illustrates the effect on DML activity that DDL statements typically have when issued. At T1, a DDL statement is issued. Subsequently, the DDL statement must wait until any DML statements that hold locks on any row of the pertinent table have completed. Also, any DML statements (1) that are issued after the DDL statement and (2) that require a lock on any row of the table must wait until the DDL statement is executed and completes.
By T2, all DML statements that were issued before the DDL statement have completed. Also at T2, the DDL statement begins executing. As part of its execution, the DDL statement acquires a lock on the table. At T3, the DDL statement completes and releases the lock on the table. The period between T3 and T4 indicates an increase in DML activity above the “normal” threshold in order to process the DML statements that were blocked on the DDL statement. In high traffic websites with online databases, the “dip” (i.e., between T1 and T3) in DML activity is unacceptable because the “dip” indicates that some users are waiting longer for database statements to complete than such users normally would wait. Thus, overall user experience suffers, which may result in some users choosing to visit other websites. Furthermore, a database system might not have enough resources to process the backlogged DML statements in addition to the DML statements that are received in the normal course of business. This lack of resources may cause additional delays in processing the DML statements.
The approaches described in this section are approaches that could be pursued, but not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated, it should not be assumed that any of the approaches described in this section qualify as prior art merely by virtue of their inclusion in this section.